package org.xtframe.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.xtframe.dao.DataBaseDao;
import org.xtframe.sql.PreparedParameter;
import org.xtframe.util.CommonUtil;
import org.xtframe.vo.FieldVO;
import org.xtframe.vo.TableColumnsVO;
import org.xtframe.vo.TableFieldVO;
import org.xtframe.vo.TableStatusVO;

//@Repository("dataBaseDao")
@Transactional
public class OracleDataBaseDaoImpl implements DataBaseDao {
	private SessionFactory sessionFactory;
	
	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public List<Map<String, Object>> executeQuery(String sql, List<PreparedParameter> preparedParameters) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Transaction tran = null;
		List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			setPreparedParameters(pstmt, preparedParameters);
			rs = pstmt.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			List<FieldVO> fields = new ArrayList<FieldVO>(columnCount);
			FieldVO field = null;
			for (int i = 1; i <= columnCount; i++) {
				field = new FieldVO();
				field.setName(rsmd.getColumnLabel(i).toLowerCase());
				field.setInt(rsmd.getColumnTypeName(i).contains("NUMBER") && rsmd.getPrecision(i)>0 && rsmd.getScale(i)==0);
				fields.add(field);
			}
			rsmd = null;
			Map<String, Object> row = null;
			while (rs.next()) {
				row = new HashMap<String, Object>(columnCount);
				for (FieldVO obj : fields) {
					if (obj.isInt()) {
						row.put(obj.getName(), rs.getInt(obj.getName()));
					} else {
						row.put(obj.getName(), rs.getString(obj.getName()));
					}
				}
				rows.add(row);
			}
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			rows = null;
			e.printStackTrace();
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rows;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public List<String> executeQuery(String sql, int columnIndex, List<PreparedParameter> preparedParameters) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Transaction tran = null;
		List<String> rows = new ArrayList<String>();
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			setPreparedParameters(pstmt, preparedParameters);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				rows.add(rs.getString(columnIndex));
			}
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			rows = null;
			e.printStackTrace();
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rows;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public int executeUpdate(String sql, List<PreparedParameter> preparedParameters) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int rows = 0;
		Transaction tran = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			setPreparedParameters(pstmt, preparedParameters);
			rows = pstmt.executeUpdate();
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			rows = -1;
			e.printStackTrace();
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rows;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public int[] excuteTransaction(List<String> sqlList, List<List<PreparedParameter>> preparedParametersList){
		Connection conn = null;
		PreparedStatement pstmt = null;
		int[] rows = null;
		Transaction tran = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement("");
			for(int i=0; i<sqlList.size(); i++){
				pstmt.addBatch(sqlList.get(i));
				if(preparedParametersList!=null && preparedParametersList.size()>i) 
					setPreparedParameters(pstmt, preparedParametersList.get(i));
			}
			rows = pstmt.executeBatch();
			tran.commit();
		} catch (Exception e) {
			tran.rollback();
			e.printStackTrace();
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return rows;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public Map<String, Object> execute(String sql) {
		Session session = null;
		Connection conn = null;
		Transaction tran = null;
		Map<String, Object> infos = new HashMap<String, Object>();
		Statement pstmt = null;
		try {
			session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.createStatement();
			pstmt.setEscapeProcessing(false);
			boolean result = pstmt.execute(sql);
			int num = 0;
			if (result) {
				ResultSet rs = pstmt.executeQuery(sql);
				if (rs.last()) {
					num = rs.getRow();
				}
			} else {
				num = pstmt.getUpdateCount();
			}
			infos.put("sucess", num);
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			infos.put("error", e.getMessage());
			infos.put("errorCode", e.getErrorCode());
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				infos.put("error", e.getMessage());
				infos.put("errorCode", e.getErrorCode());
			}
		}
		return infos;
	}

	public int insert(String sql, List<PreparedParameter> preparedParameters) throws Exception {
		throw new Exception("Oracle不支持插入返回主键方法！");
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public String findFirst(String sql, int columnIndex) {
		String result = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Transaction tran = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getString(columnIndex);
			}
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			e.printStackTrace();
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public int findRows(String sql) {
		int line = -1;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Transaction tran = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				line = rs.getInt(1);
			}
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			e.printStackTrace();
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return line;
	}

	public long findTableSize(String sql) {
		// TODO Auto-generated method stub
		return 0;
	}

	public List<TableFieldVO> findTableFields(String tableName) {
		// TODO Auto-generated method stub
		return null;
	}

	public List<TableColumnsVO> findTableColumns(String tableName) {
		// TODO Auto-generated method stub
		return null;
	}

	public List<TableStatusVO> findTableStatus(String sql) {
		// TODO Auto-generated method stub
		return null;
	}

	@SuppressWarnings("deprecation")
	@Transactional(propagation=Propagation.NOT_SUPPORTED, readOnly=true)
	public long sequenceNextVal(String name) {
		long id = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Transaction tran = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			tran = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(CommonUtil.createSql("SELECT {0}.nextval FROM dual", name));
			rs = pstmt.executeQuery();
			if (rs.next()) {
				id = rs.getLong(1);
			}
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			e.printStackTrace();
			id = -1L;
		} finally {
			try {
				if (tran != null) {
					tran = null;
				}
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return id;
	}
	
	public static void setPreparedParameters(PreparedStatement pstmt, List<PreparedParameter> preparedParameters) throws SQLException{
		if(preparedParameters != null){
			for(int i=0; i<preparedParameters.size(); i++){
				PreparedParameter pp = preparedParameters.get(i);
				int sequen = pp.getSequen()==-1 ? i+1 : pp.getSequen();
				pstmt.setString(sequen, pp.getData().toString());
			}
		}
	}
}
